package com.tisson.view;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.tisson.database.FormatUtil;
import com.tisson.database.StatementUtil;
import com.tisson.vo.DoorOpenRecordVO;

public class DoorOpenRecordView {
	private String sqlStr;

	public List findOpenType() throws SQLException {
		sqlStr = "select OpenType from DoorOpenRecord";
		Statement stmt = StatementUtil.getStatement();
		ResultSet rs = stmt.executeQuery(sqlStr);
		List list = new ArrayList();
		while (rs.next()) {
			list.add(rs.getString("OpenType"));
		}
		return list;
	}

	public List combineQuery(String name, String type, String user,
			String depart, Date begin, Date end) throws SQLException {
		String query = "select * from DoorOpenRecord,ac_user,ac_department,ac_door ";
		String where = "where DoorOpenRecord.UserId = ac_user.UserId and DoorOpenRecord.DoorId = ac_door.DoorId "
				+ "and ac_user.UserDepartment = ac_department.DepartmentId";
		String a, b, c, d, e, f;
		if (type == null || type.equals(""))
			a = " and OpenType like '%'";
		else
			a = " and OpenType = " + Integer.parseInt(type) + "";
		if (name == null || name.equals(""))
			b = " and DoorName like '%'";
		else
			b = " and DoorName like '%" + name + "%'";
		if (user == null || user.equals(""))
			c = " and UserName like '%'";
		else
			c = " and UserName = '" + user + "'";
		if (depart == null || depart.equals(""))
			d = " and DepName like '%'";
		else
			d = " and DepName = '" + depart + "'";
		if (begin == null || begin.equals(""))
			e = " and OpenTime like '%'";
		else {
			e = " and OpenTime >= #" + begin.toLocaleString() + "#";
		}
		if (end == null || end.equals(""))
			f = " and OpenTime like '%'";
		else
			f = " and OpenTime <= #" + end.toLocaleString() + "#";

		query = query + where + a + b + c + d + e + f;
		Statement stmt = StatementUtil.getStatement();
		ResultSet rs = stmt.executeQuery(query);
		List records = new ArrayList();
		while (rs.next()) {
			DoorOpenRecordVO vo = new DoorOpenRecordVO();
			vo.setDepName(rs.getString("DepName"));
			vo.setDoorId(FormatUtil.ConvertUuid(rs.getBytes("DoorId")));
			vo.setDoorName(rs.getString("DoorName"));
			vo.setOpenTime(rs.getDate("OpenTime"));
			vo.setOpenType(rs.getInt("OpenType"));
			vo.setUserId(FormatUtil.ConvertUuid(rs.getBytes("UserId")));
			vo.setUserName(rs.getString("UserName"));
			records.add(vo);
		}

		rs.close();
		stmt.close();
		return records;
	}

	public List findAll() throws SQLException {
		String sqlStr = "SELECT DoorOpenRecord.UserId,DoorOpenRecord.DoorId,AreaName,DistrictName,BuildingName,DoorName,DepName,UserName,OpenType,OpenTime "
				+ "FROM DoorOpenRecord,ac_user,ac_department,"
				+ "ac_door,ac_building,ac_district,ac_area "
				+ "WHERE "
				+ "DoorOpenRecord.UserId = ac_user.UserId AND "
				+ "ac_user.UserDepartment = ac_department.DepartmentId AND "
				+ "DoorOpenRecord.DoorId = ac_door.DoorId AND "
				+ "ac_door.DoorBuilding = ac_building.BuildingId AND "
				+ "ac_building.DistrictId = ac_district.DistrictId AND "
				+ "ac_district.AreaId = ac_area.AreaId " + "order by OpenTime";
		Statement stmt = StatementUtil.getStatement();
		ResultSet rs = stmt.executeQuery(sqlStr);
		List list = new ArrayList();
		while (rs.next()) {
			DoorOpenRecordVO vo = new DoorOpenRecordVO();
			vo.setAreaName(rs.getString("AreaName"));
			vo.setBuildingName(rs.getString("BuildingName"));
			vo.setDepName(rs.getString("DepName"));
			vo.setDistrictName(rs.getString("DistrictName"));
			vo.setDoorId(FormatUtil.ConvertUuid(rs.getBytes("DoorId")));
			vo.setDoorName(rs.getString("DoorName"));
			vo.setOpenTime(rs.getDate("OpenTime"));
			vo.setOpenType(rs.getInt("OpenType"));
			vo.setUserId(FormatUtil.ConvertUuid(rs.getBytes("UserId")));
			vo.setUserName(rs.getString("UserName"));
			list.add(vo);
		}
		rs.close();
		stmt.close();
		return list;
	}

	public List search(DoorOpenRecordVO VO, Date begin, Date end)
			throws SQLException {
		String sqlStr = "SELECT DoorOpenRecord.UserId,DoorOpenRecord.DoorId,AreaName,DistrictName,BuildingName,DoorName,DepName,UserName,OpenType,OpenTime ";
		sqlStr += "FROM DoorOpenRecord,ac_user,ac_department,";
		sqlStr += "ac_door,ac_building,ac_district,ac_area ";
		sqlStr += "WHERE ";
		if (!VO.getUserId().trim().equals("") && VO.getUserId() != null) {
			sqlStr += "DoorOpenRecord.UserId = '" + VO.getUserId() + "' ";
		}
		if (!VO.getDoorId().trim().equals("") && VO.getDoorId() != null) {
			sqlStr += "DoorOpenRecord.DoorId = '" + VO.getDoorId() + "' ";
		}
		if (!VO.getAreaName().trim().equals("") && VO.getAreaName() != null) {
			sqlStr += "AreaName LIKE '%" + VO.getAreaName() + "%' ";
		}
		if (!VO.getDistrictName().trim().equals("")
				&& VO.getDistrictName() != null) {
			sqlStr += "DistrictName LIKE '%" + VO.getDistrictName() + "%' ";
		}
		if (!VO.getBuildingName().trim().equals("")
				&& VO.getBuildingName() != null) {
			sqlStr += "BuildingName LIKE '%" + VO.getBuildingName() + "%' ";
		}
		if (!VO.getDoorName().trim().equals("") && VO.getDoorName() != null) {
			sqlStr += "DoorName LIKE '%" + VO.getDoorName() + "%' ";
		}
		if (!VO.getDepName().trim().equals("") && VO.getDepName() != null) {
			sqlStr += "DepName LIKE '%" + VO.getDepName() + "%' ";
		}
		if (!VO.getUserName().trim().equals("") && VO.getUserName() != null) {
			sqlStr += "UserName LIKE '%" + VO.getUserName() + "%' ";
		}
		if (!VO.getOpenType().equals("") && VO.getOpenType() != null) {
			sqlStr += "OpenType = " + VO.getOpenType() + " ";
		}
		if (begin != null) {
			sqlStr += "OpenTime >= #" + begin.toLocaleString() + "# ";
		}
		if (end != null) {
			sqlStr += "OpenTime <= #" + end.toLocaleString() + "# ";
		}
		sqlStr += "DoorOpenRecord.UserId = ac_user.UserId AND "
				+ "ac_user.UserDepartment = ac_department.DepartmentId AND "
				+ "DoorOpenRecord.DoorId = ac_door.DoorId AND "
				+ "ac_door.DoorBuilding = ac_building.BuildingId AND "
				+ "ac_building.DistrictId = ac_district.DistrictId AND "
				+ "ac_district.AreaId = ac_area.AreaId " + "order by OpenTime";
		Statement stmt = StatementUtil.getStatement();
		ResultSet rs = stmt.executeQuery(sqlStr);
		List list = new ArrayList();
		while (rs.next()) {
			DoorOpenRecordVO vo = new DoorOpenRecordVO();
			vo.setAreaName(rs.getString("AreaName"));
			vo.setBuildingName(rs.getString("BuildingName"));
			vo.setDepName(rs.getString("DepName"));
			vo.setDistrictName(rs.getString("DistrictName"));
			vo.setDoorId(FormatUtil.ConvertUuid(rs.getBytes("DoorId")));
			vo.setDoorName(rs.getString("DoorName"));
			vo.setOpenTime(rs.getDate("OpenTime"));
			vo.setOpenType(rs.getInt("OpenType"));
			vo.setUserId(FormatUtil.ConvertUuid(rs.getBytes("UserId")));
			vo.setUserName(rs.getString("UserName"));
			list.add(vo);
		}
		rs.close();
		stmt.close();
		return list;
	}
}
